package com.billow.data
{
	import flash.data.SQLConnection;
	import flash.data.SQLMode;
	import flash.data.SQLStatement;
	import flash.events.Event;
	import flash.events.EventDispatcher;
	import flash.events.SQLErrorEvent;
	import flash.events.SQLEvent;
	import flash.filesystem.File;
	import flash.utils.ByteArray;
	
	public class SQLite
	{
		/** 从字段开头 模糊查询 */
		public static const LIKE_BEGIN:String = "begin";
		/** 从字段结尾 模糊查询 */
		public static const LIKE_END:String = "end";
		/** 从所有字段 模糊查询 */
		public static const LIKE_ALL:String = "all";
		
		private var _sqlConnect:SQLConnection;
		private var _statement:SQLStatement;
		private var _first:Boolean;
		
		/**
		 * key 长度必须为16 (创建表关键字段需要注意，如 "select"，等不能使用)
		 * @param file
		 * @param key
		 * 
		 */		
		public function SQLite(file:File ,key:String=null)
		{
			try{
				_first = file.exists;
				_sqlConnect = new SQLConnection();
				
				if(key)
				{
					var sqlKey:ByteArray = new ByteArray(); 
					sqlKey.writeUTFBytes(key);
					_sqlConnect.open(file ,SQLMode.CREATE ,true ,1024 ,sqlKey);
				}else{
					_sqlConnect.open(file ,SQLMode.CREATE);
				}
				
				_statement = new SQLStatement();
				_statement.addEventListener(SQLEvent.RESULT ,result);
				_statement.sqlConnection = _sqlConnect;
			}catch(e:Error){
				trace(e.message);
			}
		}
		
		
		
		////////////////////////////////////////////////////////////////////
		//
		//  interface
		//
		///////////////////////////////////////////////////////////////////
		public function createTable(table:String, values:Object, autoKey:Boolean=true):void
		{
			try{
				createTableHandler(table, values, autoKey);
			}catch(e:Error){error(e);}
		}
		
		public function createData(table:String, values:Object):void
		{
			try{
				createDataHandler(table, values);
			}catch(e:Error){error(e);}
		}
		
		public function removeData(table:String, values:Object=null):void
		{
			try{
				removeDataHandler(table, values);
			}catch(e:Error){error(e);}
		}
		
		public function update(table:String, values:Object, condition:Object=null):void
		{updateHandler(table, values, condition);
			/*try{
			updateHandler(table, values, condition);
			}catch(e:Error){error();}*/
		}
		
		public function query(table:String, value:Array=null, condition:Object=null):Array
		{
			try{
				queryHandler(table, value, condition);
				return _statement.getResult().data;
			}catch(e:Error){error(e);}
			return null;
		}
		
		public function fuzzyQuery(table:String ,key:String ,like:String ,type:String ,value:Array=null):Array
		{
			try{
				fuzzyQueryHandler(table ,key ,like ,type ,value);
				return _statement.getResult().data;
			}catch(e:Error){error(e);}
			return null;
		}
		
		
		public function get lastInsertRowID():Number
		{
			return _statement.sqlConnection.lastInsertRowID;
		}
		
		public function get first():Boolean
		{
			return !_first;
		}
		
		
		
		
		
		
		
		////////////////////////////////////////////////////////////////////
		//
		//  Handler
		//
		///////////////////////////////////////////////////////////////////
		
		/** 操作成功事件 */
		private function result(event:SQLEvent):void {}
		
		/** 操作失败事件 */
		private function error(e:Error):void {trace(e.message);}
		
		/** 创建表处理 */
		private function createTableHandler(table:String, values:Object, autoKey:Boolean=true):void
		{
			_statement.clearParameters();
			
			var order:String = "CREATE TABLE IF NOT EXISTS " + table;
			var keyStr:String = "";
			for (var key:* in values)
			{
				keyStr +=  "," + key + " " + values[key]+" ";
			}
			keyStr = keyStr.substr(0 ,keyStr.length-1);
			keyStr = autoKey ? keyStr:keyStr.substr(1,keyStr.length);
			order+="("+(autoKey?"uid INTEGER PRIMARY KEY AUTOINCREMENT ":"")+keyStr+")";
			
			_statement.text = order;
			_statement.execute();
		}
		
		
		
		/** 创建数据处理 */
		private function createDataHandler(table:String, values:Object):void
		{
			_statement.clearParameters();
			
			var order:String = "insert into "+table;
			var keyStr:String="";
			var valStr:String="";
			for (var key:* in values)
			{
				keyStr+=key+",";
				valStr+=":"+key+"Val,";
				_statement.parameters[":"+key+"Val"] = values[key];
			}
			
			keyStr = keyStr.substr(0 ,keyStr.length-1);
			valStr = valStr.substr(0 ,valStr.length-1);
			
			order+="("+keyStr+") values "+"("+valStr+")";
			
			_statement.text = order;
			_statement.execute();
		}
		
		
		/** 删除数据处理 */
		private function removeDataHandler(table:String, values:Object=null):void
		{
			_statement.clearParameters();
			var order:String = "delete from "+table;
			if(values!=null)
			{
				order+=" where";
				for (var key:* in values)
				{
					order+=" "+key+"=:"+key+"Val";
					_statement.parameters[":"+key+"Val"] = values[key];
				}
			}
			
			_statement.text = order;
			_statement.execute();
		}
		
		/** 修改数据处理 */
		private function updateHandler(table:String, values:Object, condition:Object=null):void
		{
			_statement.clearParameters();
			
			var order:String = "update "+table+" set";
			for (var key:* in values)
			{
				order+=" "+key+"=:"+key+"Val,"
				_statement.parameters[":"+key+"Val"] = values[key];
			}
			
			order = order.substr(0 ,order.length-1);
			
			if(condition!=null)
			{
				order += " where";
				
				for (var key2:* in condition)
				{
					order+=" "+key2+"=:"+key2+"Val2";
					_statement.parameters[":"+key2+"Val2"] = condition[key2];
				}
			}
			
			_statement.text = order;
			_statement.execute();
		}
		
		/** 查询数据 */
		private function queryHandler(table:String, value:Array=null, condition:Object=null):void
		{
			_statement.clearParameters();
			
			var order:String = "select";
			
			if(value==null)
			{
				order+=" *"
			}else{
				for (var i:int=0;i<value.length;i++)
				{
					order+= " "+value[i];
					if(i<value.length-1)order+=",";
				}
			}
			
			order+=" from "+table;
			
			if(condition!=null)
			{
				order+=" where";
				for (var key:* in condition)
				{
					order+=" "+key+"=:"+key+"Val";
					_statement.parameters[":"+key+"Val"] = condition[key];
				}
			}
			
			_statement.text = order;
			_statement.execute();
		}
		
		
		/** 模糊查询数据 */
		private function fuzzyQueryHandler(table:String ,key:String ,like:String ,type:String ,value:Array=null):void
		{
			_statement.clearParameters();
			
			var order:String = "select";
			
			if(value==null)
			{
				order+=" *"
			}else{
				for (var i:int=0;i<value.length;i++)
				{
					order+= " "+value[i];
					if(i<value.length-1)order+=",";
				}
			}
			
			order+=" from "+table+" where "+key+" like '";
			
			switch(type)
			{
				case LIKE_BEGIN:
					order+=like+"%";
					break;
				
				case LIKE_END:
					order+="%"+like;
					break;
				
				case LIKE_ALL:
					order+="%"+like+"%";
					break;
			}
			
			order+="'";
			
			_statement.text = order;
			_statement.execute();
		}
	}
}